import pool from '../database';
import type { ReservationData } from '../../types/DataTypes';

export class ReservationDAO {
    // 新增预订
    static async addReservation(reservation: ReservationData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `INSERT INTO Reservation (customerName, reservationType, reservationKey)
                 VALUES (?, ?, ?)`,
                [reservation.customerName, reservation.reservationType, reservation.reservationKey]
            );
            return (result as any).affectedRows > 0;
        } catch (error: any) {
            if (error.code === 'ER_DUP_ENTRY') {
                throw new Error(`预订记录已存在`);
            }
            throw error;
        } finally {
            connection.release();
        }
    }

    // 获取所有预订
    static async getAllReservations(): Promise<ReservationData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Reservation ORDER BY reservationType'
            );
            return rows as ReservationData[];
        } finally {
            connection.release();
        }
    }

    // 根据客户名查询预订
/*    static async getReservationsByCustomer(customerName: string): Promise<ReservationData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Reservation WHERE customerName = ? ORDER BY reservationType',
                [customerName]
            );
            return rows as ReservationData[];
        } finally {
            connection.release();
        }
    }*/

    // 根据预订类型查询（支持单个或多个类型）
   /* static async getReservationsByType(reservationTypes: number | number[]): Promise<ReservationData[]> {
        const connection = await pool.getConnection();
        try {
            let query = 'SELECT * FROM Reservation WHERE reservationType IN (';
            const params: any[] = [];

            if (Array.isArray(reservationTypes)) {
                // 多个类型
                const placeholders = reservationTypes.map(() => '?').join(',');
                query += placeholders + ') ORDER BY reservationType, reservationKey';
                params.push(...reservationTypes);
            } else {
                // 单个类型
                query += '?) ORDER BY reservationKey';
                params.push(reservationTypes);
            }

            const [rows] = await connection.execute(query, params);
            return rows as ReservationData[];
        } finally {
            connection.release();
        }
    }*/

    // 综合查询（支持多种条件组合）
    static async searchReservations(criteria: {
        customerName?: string;
        reservationTypes?: number[];
    }): Promise<ReservationData[]> {
        const connection = await pool.getConnection();
        try {
            let query = 'SELECT * FROM Reservation WHERE 1=1';
            const params: any[] = [];

            if (criteria.customerName) {
                query += ' AND customerName = ?';
                params.push(criteria.customerName);
            }

            if (criteria.reservationTypes && criteria.reservationTypes.length > 0) {
                const placeholders = criteria.reservationTypes.map(() => '?').join(',');
                query += ` AND reservationType IN (${placeholders})`;
                params.push(...criteria.reservationTypes);
            }

            query += ' ORDER BY reservationKey';

            const [rows] = await connection.execute(query, params);
            return rows as ReservationData[];
        } finally {
            connection.release();
        }
    }


    // 删除预订
    static async deleteReservation(reservationKey: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                'DELETE FROM Reservation WHERE reservationKey = ?',
                [reservationKey]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 检查预订是否存在
    static async reservationExists(reservationKey: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT 1 FROM Reservation WHERE reservationKey = ?',
                [reservationKey]
            );
            return (rows as any[]).length > 0;
        } finally {
            connection.release();
        }
    }
}